﻿import 'package:sqflite/sqflite.dart' hide Transaction;
import 'package:path/path.dart';
import 'package:flutter/foundation.dart';
import 'package:sqflite_common_ffi_web/sqflite_ffi_web.dart';
import '../models/transaction.dart' hide Category;
import '../models/transaction.dart' as models;

class DatabaseService {
  static final DatabaseService _instance = DatabaseService._internal();
  static Database? _database;
  static const String _dbName = 'fintrack.db';
  static const int _dbVersion = 2;

  // 单例模式
  factory DatabaseService() => _instance;

  DatabaseService._internal();

  Future<Database> get database async {
    if (_database != null) return _database!;
    _database = await _initDatabase();
    return _database!;
  }

  // 初始化数据库
  Future<Database> _initDatabase() async {
    // Web平台配置
    if (kIsWeb) {
      databaseFactory = databaseFactoryFfiWeb;
      return await openDatabase(
        'fintrack_web.db',
        version: _dbVersion,
        onCreate: _createDatabase,
        onUpgrade: _onUpgrade,
      );
    }

    // 移动端配置
    String path = join(await getDatabasesPath(), _dbName);

    // 配置数据库选项
    return await openDatabase(
      path,
      version: _dbVersion,
      onCreate: _createDatabase,
      onUpgrade: _onUpgrade,
      onConfigure: (Database db) async {
        // 在事务外配置数据库选项
        await db.rawQuery('PRAGMA foreign_keys = ON');
        await db.rawQuery('PRAGMA synchronous = NORMAL');
        await db.rawQuery('PRAGMA cache_size = 2000');
        await db.rawQuery('PRAGMA journal_mode = WAL');
      },
    );
  }

  // 创建数据库表
  Future<void> _createDatabase(Database db, int version) async {
    // 创建交易记录表
    await db.execute('''
      CREATE TABLE transactions(
        id TEXT PRIMARY KEY,
        title TEXT NOT NULL,
        amount REAL NOT NULL,
        date TEXT NOT NULL,
        type TEXT NOT NULL,
        category TEXT NOT NULL,
        note TEXT,
        currency TEXT NOT NULL DEFAULT 'CNY',
        created_at INTEGER NOT NULL,
        updated_at INTEGER NOT NULL
      )
    ''');

    // 创建索引以提高查询性能
    await db.execute(
      'CREATE INDEX idx_transactions_date ON transactions(date)',
    );
    await db.execute(
      'CREATE INDEX idx_transactions_type ON transactions(type)',
    );
    await db.execute(
      'CREATE INDEX idx_transactions_category ON transactions(category)',
    );
  }

  // 升级数据库表
  Future<void> _onUpgrade(Database db, int oldVersion, int newVersion) async {
    if (oldVersion < 2) {
      // 添加货币字段
      await db.execute('''
        ALTER TABLE transactions ADD COLUMN currency TEXT NOT NULL DEFAULT 'CNY'
      ''');

      // 添加时间戳字段
      await db.execute('''
        ALTER TABLE transactions ADD COLUMN created_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
      ''');
      await db.execute('''
        ALTER TABLE transactions ADD COLUMN updated_at INTEGER NOT NULL DEFAULT (strftime('%s','now'))
      ''');
    }
  }

  // 插入交易记录
  Future<void> insertTransaction(Transaction transaction) async {
    final db = await database;
    final now = DateTime.now().millisecondsSinceEpoch;

    await db.insert('transactions', {
      ...transaction.toMap(),
      'created_at': now,
      'updated_at': now,
    }, conflictAlgorithm: ConflictAlgorithm.replace);
  }

  // 更新交易记录
  Future<void> updateTransaction(Transaction transaction) async {
    final db = await database;
    await db.update(
      'transactions',
      {
        ...transaction.toMap(),
        'updated_at': DateTime.now().millisecondsSinceEpoch,
      },
      where: 'id = ?',
      whereArgs: [transaction.id],
    );
  }

  // 删除交易记录
  Future<void> deleteTransaction(String id) async {
    final db = await database;
    await db.delete('transactions', where: 'id = ?', whereArgs: [id]);
  }

  // 获取所有交易记录
  Future<List<Transaction>> getTransactions() async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'transactions',
      orderBy: 'date DESC',
    );
    return List.generate(maps.length, (i) => Transaction.fromMap(maps[i]));
  }

  // 获取特定类型的交易记录（收入或支出）
  Future<List<Transaction>> getTransactionsByType(TransactionType type) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'transactions',
      where: 'type = ?',
      whereArgs: [type.name],
      orderBy: 'date DESC',
    );
    return List.generate(maps.length, (i) => Transaction.fromMap(maps[i]));
  }

  // 获取特定日期范围内的交易记录
  Future<List<Transaction>> getTransactionsByDateRange(
    DateTime start,
    DateTime end,
  ) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'transactions',
      where: 'date BETWEEN ? AND ?',
      whereArgs: [start.toIso8601String(), end.toIso8601String()],
      orderBy: 'date DESC',
    );
    return List.generate(maps.length, (i) => Transaction.fromMap(maps[i]));
  }

  // 获取特定类别的交易记录
  Future<List<Transaction>> getTransactionsByCategory(
    models.Category category,
  ) async {
    final db = await database;
    final List<Map<String, dynamic>> maps = await db.query(
      'transactions',
      where: 'category = ?',
      whereArgs: [category.name],
      orderBy: 'date DESC',
    );
    return List.generate(maps.length, (i) => Transaction.fromMap(maps[i]));
  }

  // 清空所有交易记录
  Future<void> clearTransactions() async {
    final db = await database;
    await db.delete('transactions');
  }

  // 关闭数据库连接
  Future<void> close() async {
    if (_database != null) {
      await _database!.close();
      _database = null;
    }
  }
}
